﻿Public Class clsProdInfo

    Public Function getProdInfoByProdId(prod_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.prod_id,a.org_time,a.steel_seal_no,a.project_no,a.location,a.jp_type,b.prod_spec_name,c.mes_project_name FROM sf.b_prod_info a"
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id"
        szSql &= " left join sf.b_pgd_mes c on a.b_pgd_yzz_details_sys_id = c.sys_id"
        szSql &= " WHERE a.prod_id like '%" & prod_id & "%'"
        Return DBacs.ExecuteSql(szSql, -1)
    End Function

    Public Function getProdInfoListByJp(start_time As String, end_time As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,prod_spec_name,a.jp_type,a.jp_layer_num,jp1wt,jp2wt,jp3wt,jp4wt,jp5wt,jp6wt,jp7wt,jp8wt,jp9wt,jp10wt,jp11wt,jp12wt,jp13wt,jp14wt,jp15wt,jp16wt,jp17wt,jp18wt,jp19wt,jp20wt,jp21wt,jp22wt,jp23wt,jp24wt,jp25wt,"
        szSql &= "jp_all_wt,zz_ope,zz_time FROM sf.b_prod_info a"
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id"
        szSql &= " WHERE 1=1 and zz_time >= ? and zz_time <= ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, start_time, end_time)
        Return dt
    End Function


    Public Function getProdInfoListByBhj(start_time As String, end_time As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,prod_spec_name,bhj_id,mst_value,bhj_wt,org_time FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= " left join sf.t_mst_jl c on a.bhj_id::integer = c.sys_id "
        szSql &= " WHERE 1=1 and org_time >= ? and org_time <= ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, start_time, end_time)
        Return dt
    End Function


    Public Function getProdInfoListByTrayNoAndStatus(tray_no As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_prod_info WHERE 1=1 and tray_no = ? and status = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, tray_no, status)
        Return dt
    End Function

    Public Function getProdInfoListByProject(project_code As String, project_name As String) As DataTable
        Dim szSql As String = "SELECT a.*,b.mes_project_name,b.mes_project_code,c.prod_spec_name FROM sf.b_prod_info a "
        szSql &= " left join sf.b_pgd_mes b on a.b_pgd_yzz_details_sys_id= b.sys_id "
        szSql &= " left join sf.t_prod_spec c on a.prod_spec_id= c.prod_spec_id "
        szSql &= " WHERE b.mes_project_code like '%" & project_code & "%' and b.mes_project_name like '%" & project_name & "%'"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        Return dt
    End Function

    ' 更新设定保护胶信息
    Public Function updateProdInfo(prodId As String, prodInfo As entity_ProdInfo, tray_no As String, code_jlck As String, pgd_mes_sys_id As String, type_value As String, jp_type As String) As Integer
        Dim szSql As String
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql = " select a.bhj_jp_type,b.sys_id,b.mst_value from sf.t_prod_spec a left join sf.t_mst_jl b on a.bhj_jp_type = b.mst_value where 1 = 1 and a.prod_spec_id = ? "
            'szSql = " SELECT * from sf.t_mst_jl where 1 = 1 and mst_type = ?"
            'Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, "保护胶")
            Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, prodInfo.prod_spec_id)
            szSql = " update sf.b_prod_info set status = ?, "
            szSql &= " rv_layer_num = ?, rv_layer_ope = ?, rv_layer_okng = ?, "
            szSql &= " rv_jp_layer_num = ?, rv_jp_layer_ope = ?, rv_jp_layer_okng = ?, "
            szSql &= " rv_gb_layer_num = ?, rv_gb_layer_ope = ?, rv_gb_layer_okng = ?, "
            szSql &= " rv_ope = ?, rv_mould_ope = ?, set_bhj_ope = ?, "
            szSql &= " bhj_id = ?, bhj_wt = ?, jstz_wt = ?, tzgb_wt = ?,bbp_wt = ?, gqncj_wt = ?, tzbhjl_wt = ?, is_gqncj = ?, org_ope = ?,jp_type = ?,bhj_pcno = ?,"
            If String.IsNullOrEmpty(prodInfo.location) = False Then
                szSql &= " location = ?,in_st_ope = ?, storage_time = (SELECT CURRENT_TIMESTAMP(6)),"
            End If
            szSql &= " org_time = (SELECT CURRENT_TIMESTAMP(6)), update_time = (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " where prod_id = ? "
            If String.IsNullOrEmpty(prodInfo.location) Then
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2",
                                             prodInfo.rv_layer_num, prodInfo.rv_layer_ope, prodInfo.rv_layer_okng,
                                             prodInfo.rv_jp_layer_num, prodInfo.rv_jp_layer_ope, prodInfo.rv_jp_layer_okng,
                                             prodInfo.rv_gb_layer_num, prodInfo.rv_gb_layer_ope, prodInfo.rv_gb_layer_okng,
                                             prodInfo.rv_ope, prodInfo.rv_mould_ope, prodInfo.set_bhj_ope,
                                             dt.Rows(0)("sys_id"), prodInfo.bhj_wt, prodInfo.jstz_wt, prodInfo.tzgb_wt, prodInfo.bbp_wt, prodInfo.gqncj_wt, prodInfo.tzbhjl_wt, prodInfo.is_gqncj, prodInfo.set_bhj_ope, jp_type, prodInfo.bhj_pcno,
                                             prodId)
            Else
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "3",
                                             prodInfo.rv_layer_num, prodInfo.rv_layer_ope, prodInfo.rv_layer_okng,
                                             prodInfo.rv_jp_layer_num, prodInfo.rv_jp_layer_ope, prodInfo.rv_jp_layer_okng,
                                             prodInfo.rv_gb_layer_num, prodInfo.rv_gb_layer_ope, prodInfo.rv_gb_layer_okng,
                                             prodInfo.rv_ope, prodInfo.rv_mould_ope, prodInfo.set_bhj_ope,
                                             dt.Rows(0)("sys_id"), prodInfo.bhj_wt, prodInfo.jstz_wt, prodInfo.tzgb_wt, prodInfo.bbp_wt, prodInfo.gqncj_wt, prodInfo.tzbhjl_wt, prodInfo.is_gqncj, prodInfo.set_bhj_ope, jp_type, prodInfo.bhj_pcno,
                                             prodInfo.location, prodInfo.in_st_ope, prodId)
                szSql = "SELECT * from sf.b_lh_cache_area where 1 = 1 and prod_id = ?"
                Dim dtlhCacheArea As DataTable = DBacs.ExecuteSql(szSql, -1, tray_no, prodId)
                If dtlhCacheArea.Rows.Count = 0 Then
                    '入库状态变更
                    szSql = "INSERT INTO sf.b_lh_cache_area( factory_id, wh_no, prod_id, into_time, status, into_ope, prod_spec_id, update_time)  "
                    szSql &= "VALUES ( ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", prodInfo.location, prodId, "0", prodInfo.rv_ope, type_value)
                End If
                '预组装库位空满状态变更
                szSql = "update sf.t_mst_storage_location set status = ?,update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= "where location_no = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", prodInfo.location)
            End If
            '胶料出库记录添加
            szSql = "INSERT INTO sf.b_jl_ck_out_details(ck_out_code, prod_id, mst_value, out_weight, update_time) "
            szSql &= "VALUES ( ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, code_jlck, prodId, prodInfo.jp_type, prodInfo.jp_all_wt)
            Dim bhj_w As String = Decimal.Parse(prodInfo.bhj_wt) + Decimal.Parse(prodInfo.tzbhjl_wt)
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, code_jlck, prodId, dt.Rows(0)("mst_value"), bhj_w)
            szSql = "INSERT INTO sf.b_jl_ck_out( factory_id, ck_out_code, operator, update_time) "
            szSql &= "VALUES ( ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", code_jlck, prodInfo.set_bhj_ope)
            szSql = "UPDATE sf.t_mst_jl "
            szSql &= " SET cur_weight = cur_weight - ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE mst_value = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, prodInfo.jp_all_wt, prodInfo.jp_type)
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, bhj_w, dt.Rows(0)("mst_value"))

            szSql = "SELECT b.bhj_material_code from sf.b_prod_info a left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id where 1 = 1 and a.prod_id = ?"
            Dim dtPs As DataTable = DBacs.ExecuteSql(szSql, -1, prodId)

            '胶片出库记录添加
            szSql = ""
            szSql &= "INSERT INTO sf.b_jp_ck_out_details(ck_out_code, oper_type, material_code, out_num, location, operator, update_time, validity) "
            szSql &= "VALUES ( ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), ?)"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, String.Empty, "0", jp_type, prodInfo.rv_jp_layer_num, String.Empty, prodInfo.set_bhj_ope, "1")
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, String.Empty, "1", dtPs.Rows(0)("bhj_material_code").ToString(), "1", String.Empty, prodInfo.set_bhj_ope, "1")
            szSql = ""
            szSql &= " select * from sf.b_jp_ck where material_code = ? "
            Dim dtJP As DataTable = DBacs.ExecuteSql(szSql, -1, jp_type)
            szSql = ""
            If dtJP.Rows.Count = 0 Then
                szSql &= "INSERT INTO sf.b_jp_ck (oper_type, material_code, cur_num, update_time) "
                szSql &= "VALUES ( ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", jp_type, "-" & prodInfo.rv_jp_layer_num)
            ElseIf dtJP.Rows.Count = 1 Then
                szSql &= "UPDATE sf.b_jp_ck  "
                szSql &= " SET cur_num = cur_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE oper_type = ? and material_code = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, prodInfo.rv_jp_layer_num, "0", jp_type)
            End If
            szSql = ""
            szSql &= " select * from sf.b_jp_ck where material_code = ? "
            Dim dtBHJP As DataTable = DBacs.ExecuteSql(szSql, -1, dtPs.Rows(0)("bhj_material_code").ToString())
            szSql = ""
            If dtBHJP.Rows.Count = 0 Then
                szSql &= "INSERT INTO sf.b_jp_ck (oper_type, material_code, cur_num, update_time) "
                szSql &= "VALUES ( ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", dtPs.Rows(0)("bhj_material_code").ToString(), "-1")
            ElseIf dtBHJP.Rows.Count = 1 Then
                szSql &= "UPDATE sf.b_jp_ck  "
                szSql &= " SET cur_num = cur_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE oper_type = ? and material_code = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", "1", dtPs.Rows(0)("bhj_material_code").ToString())
            End If

            szSql = "UPDATE sf.b_pgd_mes SET finish_count = finish_count + 1, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, pgd_mes_sys_id)
            '更新托盘主档忙闲字段
            szSql = "update sf.t_mst_tp set status = ? where tp_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", tray_no)
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function updateProdInfoJpType(prodId As String, jp_type As String) As Integer
        Dim szSql As String = "update sf.b_prod_info set jp_type = ? where prod_id = ? "
        Return DBacs.ExecuteUpdateSql(szSql, -1, jp_type, prodId)
    End Function

End Class
